package com.example.demo.mapper;

import com.example.demo.entity.Plan;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Mapper
public interface PlanMapper {

    /**
     * 查询进度表中章节记录
     * */
    @Select("select * from plan where user_id=#{uid}")
    public List<Plan> selplan(Integer uid);

    /**
     * 提交进度
     * */
    @Insert("insert into plan values(default,#{uid},#{tpid},#{cnode},#{score},#{tscore})")
    public int inplan(Integer uid,Integer tpid,Integer cnode,Integer score,Integer tscore);

    /**
     * 查询NODE数据量，若两表count相等，则开放chapter_id=count
     * */
    @Select("select count(*) from plan where chapter_node=#{cnode} and user_id = #{uid}")
    public int selpnode(Integer cnode,Integer uid);
    @Select("select count(*) from chapter where chapter_node=#{cnode}")
    public int selcnode(Integer cnode);

    /**
     * 查询进度表中node最大值用以判断考试进度
     * */
    @Select("select MAX(chapter_node) from plan where user_id=#{uid}")
    public int selmnode(Integer uid);

    /**
     * 查询node最大值对应的chapter_id
     * */
    @Select("SELECT tp.chapter_id FROM test_paper AS tp LEFT JOIN plan ON tp.test_paper_id = plan.test_paper_id WHERE plan.chapter_node = #{cnode}")
    public int selmnodecpid(Integer cnode);

    /**
     *开启考试
     *  */
    @Insert("insert into plan values(default,#{uid},1111,1,0,0)")
    public int uplan(Integer uid);

    /**
     * 进入下一章节,添加临时进度数据
     * */
    @Insert("insert into plan values(default,#{uid},1111,#{cnode},0,0)")
    public int incnodemax(Integer uid,Integer cnode);

    /**
     * 判断成绩是否达标
     */
    @Select("SELECT tp.test_paper_name,tp.chapter_id FROM plan AS p LEFT JOIN test_paper AS tp ON p.test_paper_id=tp.test_paper_id WHERE user_id = #{uid} AND chapter_node = #{cnode} AND score/total_score < 0.6")
    List<Map> selstandard(Integer uid,Integer cnode);

    /**
     * 查询是否有临时进度数据
     * */
    @Select("select * from plan where user_id=#{uid} and test_paper_id=1111 and chapter_node=#{cnode}")
    public List<Plan> sellsdata(Integer uid,Integer cnode);

    /**
     * 删除临时进度数据
     * */
    @Delete("delete from plan where user_id=#{uid} and test_paper_id=1111 and chapter_node=#{cnode}")
    public int delucnode(Integer uid,Integer cnode);

    /**
     * 查询成绩
     * */
    @Select("SELECT plan.score,plan.total_score FROM test_paper AS tp LEFT JOIN plan ON tp.test_paper_id = plan.test_paper_id WHERE tp.chapter_id = #{cid} AND plan.user_id = #{uid}")
    public Plan selscore(Integer cid, Integer uid);

    /**
     * 排行查询
     * */
    @Select("SELECT p.user_id,score,ROW_NUMBER() OVER() AS nums FROM plan AS p LEFT JOIN USER AS u ON u.user_id = p.user_id" +
            " LEFT JOIN test_paper AS t ON p.test_paper_id = t.test_paper_id " +
            "WHERE p.test_paper_id != 1111 AND p.test_paper_id=#{tid} ORDER BY p.score DESC")
    public List<HashMap> selranking(Integer tid);//当前用户当前章节排名
    //
    @Select("SELECT score,COUNT(*) as nums FROM plan WHERE test_paper_id = #{tid} GROUP BY score")
    public List<Map> selcount(Integer tid);//分数对应的人数
    @Select("SELECT p.score,u.user_name FROM plan AS p LEFT JOIN USER AS u ON u.user_id = p.user_id WHERE p.score=(SELECT MIN(score) FROM plan WHERE test_paper_id=#{tid}) AND p.test_paper_id=#{tid}")
    public List<Map> selminscore(Integer tid);//最高分
    @Select("SELECT p.score,u.user_name FROM plan AS p LEFT JOIN USER AS u ON u.user_id = p.user_id WHERE p.score=(SELECT MAX(score) FROM plan WHERE test_paper_id=#{tid}) AND p.test_paper_id=#{tid}")
    public List<Map> selmaxscore(Integer tid);//最低分

    /**
     * 试卷重做
     */
    @Delete("delete from plan where user_id=#{uid} and test_paper_id=#{tpid}")
    int delplan(Integer uid,Integer tpid);//删除进度
    @Delete("delete from record where user_id=#{uid} and test_paper_id=#{tpid}")
    int delrec(Integer uid,Integer tpid);//删除记录



}
